package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.constant.Constant;
import com.dy.yunying.api.datacenter.dto.UserBehaviorDto;
import com.dy.yunying.api.datacenter.vo.*;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author leisw
 * @date 2022/8/19 17:02
 */
@Slf4j
@Component(value = "UserBehaviorDataDao")
public class UserBehaviorDataDao {
	@Resource(name = "hbdataclickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickDcSessionTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	public Long count(UserBehaviorDto req){
		StringBuilder countSql=getAllCountSql(req);
		log.info("用户明细数据查询SQL: [\n{}]", countSql.toString());
		long start = System.currentTimeMillis();
		Long userBehaviorVoCount = clickhouseTemplate.queryForObject(countSql.toString(),Long.class);
		log.info("用户明细数据查询耗时: {}ms", System.currentTimeMillis() - start);
		return userBehaviorVoCount;
	}

	public List<UserBehaviorVo> list(UserBehaviorDto req) {
		List<UserBehaviorVo> userBehaviorVoList=new ArrayList<>();
		try {
			StringBuilder sql = getUserBehaviorSql(req);
			log.info("用户明细数据查询SQL: [\n{}]", sql.toString());
			long start = System.currentTimeMillis();
			userBehaviorVoList = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserBehaviorVo.class));
			log.info("用户明细数据查询耗时: {}ms", System.currentTimeMillis() - start);
			return userBehaviorVoList;
		}catch (Exception e){
			log.error("查询用户明细数据失败:{}",e);
			return userBehaviorVoList;
		}
	}

	private StringBuilder getAllCountSql(UserBehaviorDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT \t\n" +
				"\tCOUNT(1)\n" +
				"FROM\n")
				.append("     ").append(yunYingProperties.getNoticeDxView().substring(0,27)).append("\n")
				.append("     ").append("WHERE\n")
				.append("     ").append("    1 = 1 \n")
				.append("     ").append("and dx_event_name IN('pangu_user_behavior_platform_reg','pangu_user_behavior_login','pangu_user_behavior_enter_game') ").append("\n")
				.append("and dx_part_date >=").append("  ").append("'").append(req.getStartTime()).append("'").append("\n")
				.append("and dx_part_date <=").append("  ").append("'").append(req.getEndTime()).append("'").append("\n")
				.append(this.getWhereCommCond(req)).append('\n');
		return sql;
	}




	private StringBuilder getUserBehaviorSql(UserBehaviorDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT \t\n" +
				"\tFROM_UNIXTIME(toInt32(dx_event_time / 1000),'%Y-%m-%d %H:%M:%S') AS  behaviorTime,--行为时间\n" +
				"\tdx_event_name AS userBehavior,--行为\n" +
				"\tcase when dx_event_name = 'pangu_user_behavior_platform_reg' then '注册' \n" +
				"\twhen dx_event_name = 'pangu_user_behavior_login' then '登录'  \n" +
				"\twhen dx_event_name = 'pangu_user_behavior_enter_game' then '进入游戏' end as userBehaviorName, --行为,名称  \n" +
				"\tif(empty(mobile)=0,mobile,'-') AS mobile,--手机号\n" +
				"\tusername,--账号id\n" +
				"\tuuid,--设备id\n" +
				"\tosname,--登录端口\n" +
				"\tchl ,--子渠道\n" +
				"\tapp_chl as appchl,--分包渠道 \n" +
				"\tgameid ,--子游戏id\n" +
				"\tif(empty(areaid)=0,areaid,'-') AS areaid ,--区服\n" +
				"\tif(empty(roleid)=0,roleid,'-') AS roleid ,--角色id\n" +
				"\tif(empty(rolename)=0,rolename,'-') AS rolename ,--角色名称\n" +
				"\tif(empty(rolelevel)=0,rolelevel,'-') AS rolelevel ,--角色等级\n" +
				"\tdx_ip as ip,--登录ip\n" +
				"\tCONCAT(dx_country,'/',dx_province,'/',dx_city) as area,--地区\n" +
				"\tCONCAT(device_brand,'--',device_model) as deviceBrand,--手机品牌\n" +
				"\tos_version AS deviceModel --手机系统\n" +
				"FROM\n")
				.append("     ").append(yunYingProperties.getNoticeDxView().substring(0,27)).append("\n")
				.append("     ").append("WHERE\n")
				.append("     ").append("1 = 1 \n")
				.append("     ").append("and dx_event_name IN('pangu_user_behavior_platform_reg','pangu_user_behavior_login','pangu_user_behavior_enter_game') ").append("\n")
				.append("and dx_part_date >=").append("  ").append("'").append(req.getStartTime()).append("'").append("\n")
				.append("and dx_part_date <=").append("  ").append("'").append(req.getEndTime()).append("'").append("\n")
				.append(this.getWhereCommCond(req)).append("\n");
		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    dx_event_time DESC\n");
		}
		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}
		return sql;
	}


	/**
	 * 获得行为数据下拉框
	 * @return
	 */
	public List<UserBehaviorDimIpVo> getDimIP(String ip) {
		StringBuilder sql = new StringBuilder();
		sql.append("select \t\n" +
				"\tdx_ip as ip, \n" +
				"\tCONCAT(dx_country,'/',dx_province,'/',dx_city) as area--地区\n" +
				"\tFROM\n")
				.append("     ").append(yunYingProperties.getIpTable()).append("\n")
				.append("     ").append("WHERE\n")
				.append("     ").append("1 = 1 \n")
				.append(" AND dx_ip IN ('").append(ip.replaceAll(Constant.COMMA, "','")).append("')");
		log.info("查詢ip对应的地区数据查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<UserBehaviorDimIpVo> userBehaviorVoList = clickDcSessionTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserBehaviorDimIpVo.class));
		log.info("查詢ip对应的地区数据查询耗时: {}ms", System.currentTimeMillis() - start);
		return userBehaviorVoList;
	}



	/**
	 * 获得行为数据下拉框
	 * @param req
	 * @return
	 */
	public List<UserBehaviorScreenVo> getUserBehavior(UserBehaviorDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("select \t\n" +
				"\tdx_event_name as userBehavior, \n" +
				"\tcase when dx_event_name = 'pangu_user_behavior_platform_reg' then '注册' \n" +
				"\twhen dx_event_name = 'pangu_user_behavior_login' then '登录'  \n" +
				"\twhen dx_event_name = 'pangu_user_behavior_enter_game' then '进入游戏' end as userBehaviorName  \n" +
				"\tFROM\n")
				.append("     ").append(yunYingProperties.getNoticeDxView().substring(0,27)).append("\n")
				.append("     ").append("WHERE\n")
				.append("     ").append("    1 = 1 \n")
				.append("     ").append("and dx_event_name IN('pangu_user_behavior_platform_reg','pangu_user_behavior_login','pangu_user_behavior_enter_game') ").append("\n")
				.append("     ").append("group by dx_event_name");
		log.info("行为数据下拉框数据查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<UserBehaviorScreenVo> userBehaviorVoList = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserBehaviorScreenVo.class));
		log.info("行为数据下拉框数据查询耗时: {}ms", System.currentTimeMillis() - start);
		return userBehaviorVoList;
	}


	/**
	 * 获得数据来源下拉框
	 * @param req
	 * @return
	 */
	public List<UserBehaviorOsNameVo> getOsName(UserBehaviorDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT \n" +
				"'web' as os,\n" +
				"'Web' as osname\n" +
				"union all\n" +
				"SELECT \n" +
				"'H5' as os,\n" +
				"'H5' as osname\n" +
				"union all\n" +
				"SELECT \n" +
				"'Android' as os,\n" +
				"'Android' as osname\n" +
				"union all\n" +
				"SELECT \n" +
				"'IOS' as os,\n" +
				"'IOS' as osname\n\n");
		log.info("数据来源下拉框数据查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<UserBehaviorOsNameVo> userBehaviorVoList = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserBehaviorOsNameVo.class));

		log.info("数据来源下拉框数据查询耗时: {}ms", System.currentTimeMillis() - start);
		return userBehaviorVoList;
	}

	/**
	 * 全局通用过滤条件
	 */
	private String getWhereCommCond(UserBehaviorDto searchVo) {
		//父游戏
		final String pgidArr = searchVo.getPgidArr();
		//子游戏
		final String gameidArr = searchVo.getGameidArr();
		//主渠道列表
		final String parentchlArr = searchVo.getParentchlArr();
		//子游戏列表
		final String chlArr = searchVo.getChlArr();
		//分包渠道
		final String appchlArr = searchVo.getAppchlArr();
		//登录行为
		final String userBehavior = searchVo.getUserBehavior();
		//登录端口
		final String os = searchVo.getOs();
		//手机号
		final String mobile = searchVo.getMobile();
		//账号id
		final String accountId = searchVo.getAccountId();
		//角色id
		final String playerId = searchVo.getPlayerId();
		//设备id
		final String uuid = searchVo.getUuid();

		// 通用筛选条件
		final StringBuilder commCondSB = new StringBuilder();
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND gameid IN (").append(gameidArr).append(")");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			commCondSB.append(" AND app_chl IN ('").append(appchlArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if (StringUtils.isNotBlank(userBehavior)) {
			commCondSB.append(" AND dx_event_name IN ('").append(userBehavior.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if (StringUtils.isNotBlank(os)) {
			commCondSB.append(" AND osname IN ('").append(os.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if (StringUtils.isNotBlank(mobile)) {
			commCondSB.append(" AND mobile = '").append(mobile).append("'");
		}
		if (StringUtils.isNotBlank(accountId)) {
			commCondSB.append(" AND username = '").append(accountId).append("'");
		}
		if (StringUtils.isNotBlank(playerId)) {
			commCondSB.append(" AND roleid = '").append(playerId).append("'");
		}
		if (StringUtils.isNotBlank(uuid)) {
			commCondSB.append(" AND uuid = '").append(uuid).append("'");
		}

		return commCondSB.toString();
	}

}
